<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class CatController extends Controller
{
    //栏目
    public function show(Request $request,$cid)
    {
        $q = request()->input('q');

        //分类
        $cats = DB::table('cat')->join('art', 'art.cat_id', '=', 'cat.cat_id')->select('cat.cat_id','cat.catname',DB::raw('count(art.cat_id) as num'))->groupBy('art.cat_id')->get();
        $cat = array_column(\GuzzleHttp\json_decode(\GuzzleHttp\json_encode($cats),true),null,'cat_id');

        $tdk = [];
        $tdk['title'] = $cat[$cid]['catname'].'-侯体宗的博客';
        $tdk['keywords'] = $cat[$cid]['catname'].'栏目_侯体宗的博客';
        $tdk['description'] = $cat[$cid]['catname'].'栏目，博客，侯体宗，侯体宗的博客';

        //文章
        if (isset($q)) {
            if ($q == 'hot') {
                $art = DB::table('art')->select('art_id', 'title', 'content', 'pubtime', 'view', 'cat_id')->where('cat_id',$cid)->where('is_state', 0)->where('is_del', 1)->orderBy('view', 'desc')->paginate(30);
            }else {
                $art = DB::table('art')->select('art_id', 'title', 'content', 'pubtime', 'view', 'cat_id')->where('cat_id',$cid)->where('is_state', 0)->where('is_del', 1)->where('title','like',"%{$q}%")->paginate(30);
            }
        }else{
            $art = DB::table('art')->select('art_id','title','content','pubtime','view','cat_id')->where('cat_id',$cid)->where('is_state',0)->where('is_del',1)->orderBy('art_id','desc')->paginate(30);
        }

        //近期评论 取出10条
       // $sql = 'select t1.nick,t1.comment,t1.art_id,t2.title from comment as t1 inner join art as t2 on t1.art_id=t2.art_id order by t1.pubtime desc limit 5';


        //文章归档 格式：2017年01月
        //$sql = "select pubtime,FROM_UNIXTIME( `pubtime`,'%Y-%m') as time ,count(*) as num FROM art where  is_state=0 and is_del=1 group by time" ;
        $file = DB::table('art')->select('pubtime',DB::raw('FROM_UNIXTIME(pubtime,\'%Y-%m\') as time'),DB::raw('count(*) as num'))->where('cat_id',$cid)->where('is_state',0)->where('is_del',1)->groupBy('time')->get();

        //标签
        //$tags = Db::name('tag')->group('tagname')->order('tag_id desc')->limit(50)->column('tagname','tag_id');
        $tags = DB::table('tag')->select('*')->groupBy('tagname')->orderBy('tag_id','desc')->take(50)->pluck('tagname','tag_id');


        return view('home.index.index', ['tdk'=>$tdk,'tags'=>$tags,'file'=>$file,'art'=>$art,'cats'=>$cats]);
    }



}
